package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @program: spms
 * @description:
 * @author: Wayne Wu
 * @create: 2021-10-21 15:36
 **/
@Mapper
public interface SapRepackageMapper {

    @Select("SELECT SPN.PACKAGE_NOTE AS \"packageNo\",\n" +
            "       SPN.DELIVERY_NOTE_SRM AS \"srmDn\",\n" +
            "       SPN.DELIVERY_NOTE_SAP AS \"sapDn\",\n" +
            "       SPN.SUPPLIER_CODE AS \"lifnr\",\n" +
            "       TO_CHAR(SPN.TOTAL_VOLUME) AS \"volumT\",\n" +
            "       TO_CHAR(SPN.TOTAL_WEIGHT) AS \"brgewT\",\n" +
            "       SPN.LOAD_BEGIN_TIME AS \"zxdateF\",\n" +
            "       SPN.LOAD_END_TIME AS \"zxdateT\",\n" +
            "       SPN.TRANSPORTER AS \"ysr\",\n" +
            "       SPN.CONTRACTOR_INFO AS \"cyrlxfs\",\n" +
            "       SPN.RECEIVER AS \"shr\",\n" +
            "       SPN.RECEIVER_INFO AS \"shrlxfs\",\n" +
            "       SPN.RECEIVING_ADDRESS AS \"shdz\",\n" +
            "       SPN.SPMS_CONTRACT_NOTE AS \"spmsContractNo\",\n" +
            "       'S' AS \"spmsFlg\"\n" +
            "FROM SAP_PACKAGE_NOTE SPN, SAP_BOX_NOTE SBN\n" +
            "WHERE SPN.PACKAGE_NOTE = SBN.PACKAGE_NOTE\n" +
            "  AND SBN.BOX_NOTE = #{boxNote} ")
    Map<String, Object> getHeadToSap(@Param("boxNote") String boxNote);

    @Select("SELECT SBN.PACKAGE_NOTE AS \"packageNo\",\n" +
            "       SBN.BOX_NOTE AS \"packageCode\",\n" +
            "       NVL(SBN.BOX_MATERIAL_NAME, '') AS \"outerBoxTexture\",\n" +
            "       TO_CHAR(SBN.TOTAL_QTY) AS \"totalQty\",\n" +
            "       TO_CHAR(SBN.BOX_GROSS_WEIGHT) AS \"boxBrgew\",\n" +
            "       TO_CHAR(SBN.BOX_VOLUME) AS \"boxVolum\",\n" +
            "       TO_CHAR(SBN.BOX_LENGTH) AS \"length\",\n" +
            "       TO_CHAR(SBN.BOX_WIDTH) AS \"width\",\n" +
            "       TO_CHAR(SBN.BOX_HEIGHT) AS \"height\",\n" +
            "       NVL(SBN.BOX_COLOR, '') AS \"color\"\n" +
            "FROM SAP_BOX_NOTE SBN\n" +
            "WHERE SBN.BOX_NOTE = #{boxNote} ")
    Map<String, Object> boxNoteToSap(@Param("boxNote") String boxNote);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT WSI.BOX_NOTE AS \"boxNote\",\n" +
            "           WSI.UUID AS \"uuid\",\n" +
            "           WS.WERKS AS \"werks\",\n" +
            "           WS.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "           WS.STORAGE_NO AS \"storageNo\",\n" +
            "           SBN.PARENT_BOX_NOTE AS \"parentBoxNote\",\n" +
            "           (SELECT LISTAGG(SBN2.BOX_NOTE, ',')\n" +
            "               WITHIN GROUP ( ORDER BY SBN.BOX_NOTE)\n" +
            "           FROM SAP_BOX_NOTE SBN2\n" +
            "           WHERE SBN.BOX_NOTE = SBN2.PARENT_BOX_NOTE) AS \"childSapBoxNote\"\n" +
            "    FROM WHS_STORAGE_INVENTORY WSI\n" +
            "        LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        LEFT JOIN SAP_BOX_NOTE SBN ON WSI.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    WHERE SBN.BOX_NOTE IS NOT NULL\n" +
            "          AND WSI.QUANTITY > 0\n" +
            "      AND WS.WERKS = #{werks}\n" +
            "      AND WS.WHS_LOCATION_CODE = #{whsLocationCode}\n" +
            "      <when test='boxNote!=null'> AND SBN.BOX_NOTE = #{boxNote} </when> \n" +
            "    ORDER BY \"childSapBoxNote\" NULLS LAST, SBN.BOX_NOTE ASC\n" +
            "    ) RE" +
            "</script>")
    List<Map<String, Object>> pageMaDataMap(Page<Map<String, Object>> pages,
                                            @Param("werks") String werks,
                                            @Param("whsLocationCode") String whsLocationCode,
                                            @Param("boxNote") String boxNote);



}
